﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;
using CTEM.Controllers.Global;

using CTEM.Models;
using CTEM.Controllers.Global;
using System.Diagnostics;

namespace CTEM.Controllers.DAO
{
    class VH_VehicleDAO
    {
        /// <summary>
        /// 
        /// </summary>
        public VH_VehicleDAO()
        {
            Transaction = null;
        }

        /// <summary>
        /// 
        /// </summary>
        private StackFrame _currentStackFrame;

        /// <summary>
        /// 
        /// </summary>
        public SqlTransaction Transaction { get; set; }

        /// <summary>
        /// 
        /// </summary>
        private SqlConnection _con;
        
        /// <summary>
        /// 
        /// </summary>
        SqlConnection Connection
        {
            get
            {
                if (_con == null)
                    _con = new SqlConnection(DatabaseInfo.ConnectionString);
                if (_con.State == ConnectionState.Closed)
                {
                    _con.Open();
                }
                return _con;
            }
            set
            {
                _con = value;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="vehicle"></param>
        /// <returns></returns>
        public long CreateVehicle(VH_VehicleDTO vehicle)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_CreateVehicle]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@Name", 
                                                  "@Number", 
                                                  "@ManagerID", 
                                                  "@WarehouseID", 
                                                  "@ConstructionID", 
                                                  "@Status", 
                                                  "@Category", 
                                                  "@DateBought", 
                                                  "@IsActive", 
                                                  "@IsDelete", 
                                                  "@CreatedBy", 
                                                  "@CreatedDate" };
                    object[] parametervalues = { vehicle.Name, 
                                                   vehicle.Number, 
                                                   vehicle.ManagerID, 
                                                   vehicle.WarehouseID, 
                                                   vehicle.ConstructionID, 
                                                   vehicle.Status, 
                                                   vehicle.Category, 
                                                   vehicle.DateBought, 
                                                   vehicle.IsActive,
                                                   vehicle.IsDelete,
                                                   vehicle.CreatedBy,
                                                   vehicle.CreatedDate };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="vehicle"></param>
        /// <returns></returns>
        public bool UpdateVehicle(VH_VehicleDTO vehicle)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_UpdateVehicle]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@VehicleID",
                                                  "@Name", 
                                                  "@Number", 
                                                  "@ManagerID", 
                                                  "@WarehouseID", 
                                                  "@ConstructionID", 
                                                  "@Status", 
                                                  "@Category", 
                                                  "@DateBought", 
                                                  "@IsActive", 
                                                  "@IsDelete", 
                                                  "@UpdatedBy", 
                                                  "@LastUpdated" };
                    object[] parametervalues = { vehicle.VehicleID,
                                                   vehicle.Name, 
                                                   vehicle.Number, 
                                                   vehicle.ManagerID, 
                                                   vehicle.WarehouseID, 
                                                   vehicle.ConstructionID, 
                                                   vehicle.Status, 
                                                   vehicle.Category, 
                                                   vehicle.DateBought, 
                                                   vehicle.IsActive,
                                                   vehicle.IsDelete,
                                                   vehicle.UpdatedBy,
                                                   vehicle.LastUpdated };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="vehicleID"></param>
        /// <returns></returns>
        public bool DeleteVehicle(long vehicleID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_DeleteVehicle]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@VehicleID", vehicleID));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="subconID"></param>
        /// <returns></returns>
        public VH_VehicleDTO GetVehicleByID(long subconID)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_GetVehicleByID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@VehicleID", subconID));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            VH_VehicleDTO consDTO = new VH_VehicleDTO
                            {
                                VehicleID = Convert.ToInt64(reader["VehicleID"]),
                                Name = Convert.ToString(reader["Name"]),
                                Number = Convert.ToString(reader["Number"]),
                                ManagerID = Convert.ToInt64(reader["ManagerID"]),
                                WarehouseID = Convert.ToInt64(reader["WarehouseID"]),
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                Status = Convert.ToString(reader["Status"]),
                                Category = Convert.ToString(reader["Category"]),
                                DateBought = Convert.ToDateTime(reader["DateBought"]),
                                IsActive = Convert.ToBoolean(reader["IsActive"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            return consDTO;
                        }
                        return null;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isActive"></param> 
        /// <returns></returns>
        public List<VH_VehicleDTO> LoadAllVehicles(bool isActive)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_GetVehicle]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                if (isActive)
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", isActive));
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@IsActive", DBNull.Value));
                }
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<VH_VehicleDTO> listcons = new List<VH_VehicleDTO>();
                        while (reader.Read())
                        {
                            VH_VehicleDTO consDTO = new VH_VehicleDTO
                            {
                                VehicleID = Convert.ToInt64(reader["VehicleID"]),
                                Name = Convert.ToString(reader["Name"]),
                                Number = Convert.ToString(reader["Number"]),
                                ManagerID = Convert.ToInt64(reader["ManagerID"]),
                                WarehouseID = Convert.ToInt64(reader["WarehouseID"]),
                                ConstructionID = Convert.ToInt64(reader["ConstructionID"]),
                                Status = Convert.ToString(reader["Status"]),
                                Category = Convert.ToString(reader["Category"]),
                                DateBought = Convert.ToDateTime(reader["DateBought"]),
                                IsActive = Convert.ToBoolean(reader["IsActive"]),
                                IsDelete = Convert.ToBoolean(reader["IsDelete"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            listcons.Add(consDTO);
                        }
                        return listcons;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }











        /// <summary>
        /// 
        /// </summary>
        /// <param name="vhexp"></param>
        /// <returns></returns>
        public long CreateVehicleExpenses(VH_ExpensesDTO vhexp)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_CreateVehicleExpenses]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@DebtID", 
                                                  "@DatePurchase", 
                                                  "@Officer", 
                                                  "@VehicleID", 
                                                  "@Contents", 
                                                  "@MaterialID", 
                                                  "@GroupMaterialID", 
                                                  "@Quantity", 
                                                  "@Price", 
                                                  "@TotalCost", 
                                                  "@Note", 
                                                  "@CreatedBy", 
                                                  "@CreatedDate" };
                    object[] parametervalues = { vhexp.DebtID, 
                                                   vhexp.DatePurchase, 
                                                   vhexp.Officer, 
                                                   vhexp.VehicleID, 
                                                   vhexp.Contents, 
                                                   vhexp.MaterialID, 
                                                   vhexp.GroupMaterialID, 
                                                   vhexp.Price, 
                                                   vhexp.TotalCost,
                                                   vhexp.Note,
                                                   vhexp.CreatedBy,
                                                   vhexp.CreatedDate };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                            return Convert.ToInt64(reader[0]);
                    }
                    return 0;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return 0;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="vhexp"></param>
        /// <returns></returns>
        public bool UpdateVehicleExpenses(VH_ExpensesDTO vhexp)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_UpdateVehicleExpenses]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    string[] parameternames = { "@DebtID", 
                                                  "@DatePurchase", 
                                                  "@Officer", 
                                                  "@VehicleID", 
                                                  "@Contents", 
                                                  "@MaterialID", 
                                                  "@GroupMaterialID", 
                                                  "@Quantity", 
                                                  "@Price", 
                                                  "@TotalCost", 
                                                  "@Note", 
                                                  "@CreatedBy", 
                                                  "@CreatedDate" };
                    object[] parametervalues = { vhexp.DebtID, 
                                                   vhexp.DatePurchase, 
                                                   vhexp.Officer, 
                                                   vhexp.VehicleID, 
                                                   vhexp.Contents, 
                                                   vhexp.MaterialID, 
                                                   vhexp.GroupMaterialID, 
                                                   vhexp.Price, 
                                                   vhexp.TotalCost,
                                                   vhexp.Note,
                                                   vhexp.CreatedBy,
                                                   vhexp.CreatedDate };
                    cmd.Parameters.AddRange(Global.Global.SetSqlParameter(parameternames, parametervalues));
                    cmd.ExecuteReader();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="vhexpid"></param>
        /// <returns></returns>
        public bool DeleteVehicleExpenses(long vhexpid)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_DeleteVehicleExpenses]", Connection) { CommandType = CommandType.StoredProcedure })
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                try
                {
                    cmd.Parameters.Add(new SqlParameter("@VehicleExpensesID", vhexpid));
                    cmd.ExecuteNonQuery();
                    return true;
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return false;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="vhexpid"></param>
        /// <returns></returns>
        public VH_ExpensesDTO GetVehicleExpensesByID(long vhexpid)
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_GetVehicleExpensesByID]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@VehicleExpensesID", vhexpid));
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            VH_ExpensesDTO vhexp = new VH_ExpensesDTO
                            {
                                DebtID = Convert.ToInt64(reader["DebtID"]),
                                DatePurchase = Convert.ToDateTime(reader["DatePurchase"]),
                                Officer = Convert.ToString(reader["Officer"]),
                                VehicleID = Convert.ToInt64(reader["VehicleID"]),
                                Contents = Convert.ToString(reader["Contents"]),
                                MaterialID = Convert.ToInt64(reader["MaterialID"]),
                                GroupMaterialID = Convert.ToInt64(reader["GroupMaterialID"]),
                                Quantity = Convert.ToDouble(reader["Quantity"]),
                                Unit = Convert.ToString(reader["Unit"]),
                                Price = Convert.ToInt64(reader["Price"]),
                                TotalCost = Convert.ToInt64(reader["TotalCost"]),
                                Note = Convert.ToString(reader["Note"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            return vhexp;
                        }
                        return null;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="isActive"></param> 
        /// <returns></returns>
        public List<VH_ExpensesDTO> LoadAllVehicleExpensess()
        {
            _currentStackFrame = new StackFrame();
            using (var cmd = new SqlCommand("[dbo].[VH_GetAllVehicleExpenses]", Connection))
            {
                if (Transaction != null)
                {
                    cmd.Transaction = Transaction;
                }
                cmd.CommandType = CommandType.StoredProcedure;
                try
                {
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        List<VH_ExpensesDTO> listvhexp = new List<VH_ExpensesDTO>();
                        while (reader.Read())
                        {
                            VH_ExpensesDTO vhexp = new VH_ExpensesDTO
                            {
                                DebtID = Convert.ToInt64(reader["DebtID"]),
                                DatePurchase = Convert.ToDateTime(reader["DatePurchase"]),
                                Officer = Convert.ToString(reader["Officer"]),
                                VehicleID = Convert.ToInt64(reader["VehicleID"]),
                                Contents = Convert.ToString(reader["Contents"]),
                                MaterialID = Convert.ToInt64(reader["MaterialID"]),
                                GroupMaterialID = Convert.ToInt64(reader["GroupMaterialID"]),
                                Quantity = Convert.ToDouble(reader["Quantity"]),
                                Unit = Convert.ToString(reader["Unit"]),
                                Price = Convert.ToInt64(reader["Price"]),
                                TotalCost = Convert.ToInt64(reader["TotalCost"]),
                                Note = Convert.ToString(reader["Note"]),
                                CreatedBy = Convert.ToString(reader["CreatedBy"]),
                                CreatedDate = Convert.ToDateTime(reader["CreatedDate"]),
                                UpdatedBy = Convert.ToString(reader["UpdatedBy"]),
                                LastUpdated = Convert.ToDateTime(reader["LastUpdated"])
                            };
                            listvhexp.Add(vhexp);
                        }
                        return listvhexp;
                    }
                }
                catch (SqlException sql)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, sql.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                catch (Exception e)
                {
                    string msn = Global.Global.LogMessage(_currentStackFrame.GetFileName(), _currentStackFrame.GetMethod().Name, e.Message);
                    Logger.WriteLog(LogLevel.ERROR, msn);
                    return null;
                }
                finally
                {
                    if (Transaction == null)
                        Connection.Close();
                }
            }
        }
    }
}
